import tkinter as tk
from tkinter import filedialog, messagebox, colorchooser
import openpyxl
from openpyxl.styles import Font, PatternFill
import os
import tempfile
import uuid  # 用于生成临时安全文件名
from tkinter import ttk
from ttkthemes import ThemedTk
from openpyxl.styles import Font, PatternFill, Alignment  # 添加 Alignment
import xlrd  # 添加 xlrd 库
import shutil  # 用于文件操作
import win32com.client  # 添加 win32com
import pythoncom  # 添加 pythoncom
from openpyxl.utils import column_index_from_string

# 创建 GUI 窗口
# 替换原有的 root = tk.Tk() 这行
try:
    root = tk.Tk()
except Exception:
    root = ThemedTk(theme="arc")  # 回退使用 ThemedTk
root.title("Excel格式批量修改器")
root.geometry("800x470")
# Windows 缩放优化，减轻重绘卡顿
try:
    if root.tk.call("tk", "windowingsystem") == "win32":
        root.tk.call("tk", "scaling", 1.0)
except Exception:
    pass

# 在创建root窗口之后添加
style = ttk.Style()
style.configure("TButton", padding=6, relief="flat", background="#ccc")
style.configure("TFrame", padding=10)
# 性能优化：尽量使用内置轻量主题（若可用）
try:
    available_themes = set(style.theme_names())
    if "vista" in available_themes:
        style.theme_use("vista")
    elif "winnative" in available_themes:
        style.theme_use("winnative")
    elif "clam" in available_themes:
        style.theme_use("clam")
except Exception:
    pass

# 创建主框架
main_frame = ttk.Frame(root)
main_frame.pack(fill=tk.BOTH, expand=True, padx=20, pady=20)

workbooks = {}  # 用于存储加载的工作簿
original_workbooks = {}  # 用于存储原始的工作簿副本
original_workbooks_paths = {}  # 用于存储原始工作簿的临时文件路径


def load_all_excels():
    global workbooks, original_workbooks_paths
    folder_path = filedialog.askdirectory()
    if folder_path:
        try:
            # 首先关闭之前打开的所有工作簿
            for wb in workbooks.values():
                wb.close()
            workbooks = {}

            # 清理之前的临时文件
            for temp_path in original_workbooks_paths.values():
                try:
                    os.unlink(temp_path)
                except Exception:
                    pass
            original_workbooks_paths = {}

            # 加载新的Excel文件
            excel_files = [os.path.join(folder_path, f) for f in os.listdir(folder_path)
                           if f.lower().endswith(('.xlsx', '.xls'))]

            # 初始化 COM
            pythoncom.CoInitialize()
            excel = win32com.client.Dispatch("Excel.Application")
            excel.Visible = False
            excel.DisplayAlerts = False

            try:
                for file in excel_files:
                    try:
                        if file.lower().endswith('.xls'):
                            # 处理 .xls 文件
                            # 先复制到临时目录并使用简短安全的文件名，避免路径过长或非法字符
                            temp_xls_copy = os.path.join(tempfile.gettempdir(), f"{uuid.uuid4()}.xls")
                            shutil.copy2(file, temp_xls_copy)

                            # 创建临时的 xlsx 文件路径
                            temp_xlsx = os.path.join(tempfile.gettempdir(), f"{uuid.uuid4()}.xlsx")

                            # 使用 Excel COM 对象打开临时副本并另存为 xlsx
                            wb = excel.Workbooks.Open(os.path.abspath(temp_xls_copy))
                            wb.SaveAs(os.path.abspath(temp_xlsx), FileFormat=51)  # 51 表示 xlsx 格式
                            wb.Close()

                            # 删除临时 .xls 副本
                            try:
                                os.unlink(temp_xls_copy)
                            except Exception:
                                pass

                            # 使用 openpyxl 打开转换后的文件
                            workbooks[file] = openpyxl.load_workbook(temp_xlsx)
                            original_workbooks_paths[file] = temp_xlsx
                        else:
                            # 处理 .xlsx 文件
                            wb = openpyxl.load_workbook(file)
                            workbooks[file] = wb

                            # 创建临时文件副本
                            _, temp_file_path = tempfile.mkstemp(suffix='.xlsx')
                            wb.save(temp_file_path)
                            original_workbooks_paths[file] = temp_file_path
                    except Exception as e:
                        messagebox.showerror("错误", f"加载文件 {os.path.basename(file)} 时出错: {str(e)}")
                        continue
            finally:
                # 清理 COM 对象
                excel.Quit()
                pythoncom.CoUninitialize()

            if workbooks:
                messagebox.showinfo("信息", f"共加载 {len(workbooks)} 个文件.")
            else:
                messagebox.showwarning("警告", "没有成功加载任何文件!")
        except Exception as e:
            messagebox.showerror("错误", f"加载文件时出现错误: {str(e)}")
    else:
        messagebox.showerror("错误", "未选择文件夹或文件夹为空!")


def restore_originals():
    global workbooks, original_workbooks_paths
    if original_workbooks_paths:
        workbooks = {file: openpyxl.load_workbook(temp_path) for file, temp_path in original_workbooks_paths.items()}
        messagebox.showinfo("信息", "所有工作簿已还原到最初的状！")
    else:
        messagebox.showerror("错误", "没有可用于还原的原始工作簿！")


def clean_up():
    # 首先关闭所有打开的工作簿
    global workbooks
    for wb in workbooks.values():
        wb.close()
    workbooks = {}  # 清空工作簿字典

    # 然后尝试删除临时文件
    for temp_path in original_workbooks_paths.values():
        try:
            os.unlink(temp_path)
        except Exception as e:
            print(f"清理临时文件时出现错误: {e}")  # 打印错误但继续执行


def save_excels():
    global workbooks
    if workbooks:
        try:
            for path, wb in workbooks.items():
                wb.save(path)
            messagebox.showinfo("成功", f"全部 {len(workbooks)} 个文件已保存!")
        except Exception as e:
            messagebox.showerror("保存错误", f"保存失败: {str(e)}")
    else:
        messagebox.showerror("错误", "没有加载的工作簿!")


def is_merged_cell(sheet, cell):
    """检查单元格是否为合并单元格"""
    for merged_range in sheet.merged_cells.ranges:
        if cell.coordinate in merged_range:
            return True
    return False


def get_merge_master_cell(sheet, cell):
    """获取合并单元格的主单元格"""
    for merged_range in sheet.merged_cells.ranges:
        if cell.coordinate in merged_range:
            return sheet[merged_range.start_cell.coordinate]
    return cell


def apply_to_cell(cell, sheet, **kwargs):
    """统一的单元格样式应用函数"""
    if is_merged_cell(sheet, cell):
        master_cell = get_merge_master_cell(sheet, cell)
        if master_cell.coordinate == cell.coordinate:
            # 只对合并区域的主单元格应用样式
            for key, value in kwargs.items():
                setattr(cell, key, value)
    else:
        # 对非合并单元格正常应用样式
        for key, value in kwargs.items():
            setattr(cell, key, value)


def apply_fill_color():
    color_options = tk.Toplevel(root)
    color_options.title("修改背景颜色")
    color_options.geometry("800x150")

    selection_frame = ttk.Frame(color_options)
    selection_frame.pack(fill=tk.X, padx=10, pady=5)

    ttk.Label(selection_frame, text="选择范围：").pack(side=tk.LEFT)
    range_var = tk.StringVar(value="all")
    ttk.Radiobutton(selection_frame, text="所有单元格", variable=range_var, value="all").pack(side=tk.LEFT)
    ttk.Radiobutton(selection_frame, text="选定行", variable=range_var, value="rows").pack(side=tk.LEFT)
    ttk.Radiobutton(selection_frame, text="选定列", variable=range_var, value="cols").pack(side=tk.LEFT)

    # 行列选择框
    selection_input = ttk.Frame(color_options)
    selection_input.pack(fill=tk.X, padx=10, pady=5)
    ttk.Label(selection_input, text="输入行/列范围（例如：1,2,3 或 A,B,C 或 1-10,15 或 A-F,H）：").pack(side=tk.LEFT)
    range_entry = ttk.Entry(selection_input)
    range_entry.pack(side=tk.LEFT, fill=tk.X, expand=True)

    def apply():
        bg_color = colorchooser.askcolor(title="选择背景颜色")[1]
        if not bg_color:
            return

        range_type = range_var.get()
        range_value = range_entry.get().strip()

        fill = PatternFill(start_color=bg_color.replace('#', ''),
                           end_color=bg_color.replace('#', ''),
                           fill_type="solid")

        for wb in workbooks.values():
            for sheet in wb:
                if range_type == "all":
                    for row in sheet.iter_rows():
                        for cell in row:
                            apply_to_cell(cell, sheet, fill=fill)
                elif range_type == "rows" and range_value:
                    # 使用新的范围解析函数
                    rows = parse_range_expression(range_value)
                    for row_num in rows:
                        if isinstance(row_num, int):
                            for cell in sheet[row_num]:
                                apply_to_cell(cell, sheet, fill=fill)
                elif range_type == "cols" and range_value:
                    # 使用新的范围解析函数
                    cols = parse_range_expression(range_value)
                    for col in cols:
                        if isinstance(col, str) and col.isalpha():
                            for cell in sheet[col]:
                                apply_to_cell(cell, sheet, fill=fill)

        color_options.destroy()
        messagebox.showinfo("完成", "背景颜色已修改!")

    ttk.Button(color_options, text="选择颜色并应用", command=apply).pack(pady=10)


def apply_alignment():
    try:
        # 直接创建居中对齐的设置
        alignment = Alignment(horizontal='center', vertical='center')

        # 对所有打开的工作簿进行处理
        for wb in workbooks.values():
            for sheet in wb:
                # 对整个表格的所有单元格应用居中对齐
                for row in sheet.iter_rows():
                    for cell in row:
                        cell.alignment = alignment

        status_bar.config(text="单元格已居中对齐")
        messagebox.showinfo("完成", "所有单元格已设置居中对齐!")
    except Exception as e:
        messagebox.showerror("错误", f"修改过程中发生错误: {str(e)}")


def apply_font_size():
    font_size_options = tk.Toplevel(root)
    font_size_options.title("修改字体大小")
    font_size_options.geometry("560x370")

    # 创建设置框架
    settings_frame = ttk.Frame(font_size_options)
    settings_frame.pack(fill=tk.BOTH, expand=True, padx=10, pady=5)

    # 字体大小选择器
    size_frame = ttk.LabelFrame(settings_frame, text="字体大小")
    size_frame.pack(fill=tk.X, pady=5)

    size_var = tk.IntVar(value=11)
    size_scale = ttk.Scale(size_frame, from_=8, to=72, variable=size_var,
                           orient=tk.HORIZONTAL, command=lambda x: size_var.set(round(float(x))))
    size_scale.pack(fill=tk.X, padx=10, pady=5)
    size_label = ttk.Label(size_frame, textvariable=size_var)
    size_label.pack()

    # 范围选择
    range_frame = ttk.LabelFrame(settings_frame, text="应用范围")
    range_frame.pack(fill=tk.X, pady=5)

    range_var = tk.StringVar(value="all")
    ttk.Radiobutton(range_frame, text="所有单元格", variable=range_var, value="all").pack(anchor=tk.W)
    ttk.Radiobutton(range_frame, text="选定行", variable=range_var, value="rows").pack(anchor=tk.W)
    ttk.Radiobutton(range_frame, text="选定列", variable=range_var, value="cols").pack(anchor=tk.W)
    ttk.Radiobutton(range_frame, text="指定单元格", variable=range_var, value="cells").pack(anchor=tk.W)

    # 范围输入框
    input_frame = ttk.Frame(range_frame)
    input_frame.pack(fill=tk.X, pady=5)
    ttk.Label(input_frame, text="范围:").pack(side=tk.LEFT)
    range_entry = ttk.Entry(input_frame)
    range_entry.pack(side=tk.LEFT, fill=tk.X, expand=True)
    ttk.Label(input_frame, text="(例如: A1,B2 或 1-5,8 或 A-F,H)").pack(side=tk.LEFT)

    def apply():
        font_size = size_var.get()
        range_type = range_var.get()
        range_value = range_entry.get().strip()

        for wb in workbooks.values():
            for sheet in wb:
                if range_type == "all":
                    for row in sheet.iter_rows():
                        for cell in row:
                            if cell.font:
                                new_font = Font(size=font_size, name=cell.font.name, bold=cell.font.bold,
                                               italic=cell.font.italic, underline=cell.font.underline)
                            else:
                                new_font = Font(size=font_size)
                            cell.font = new_font
                elif range_type == "rows" and range_value:
                    rows = parse_range_expression(range_value)
                    for row_num in rows:
                        if isinstance(row_num, int):
                            for cell in sheet[row_num]:
                                if cell.font:
                                    new_font = Font(size=font_size, name=cell.font.name, bold=cell.font.bold,
                                                   italic=cell.font.italic, underline=cell.font.underline)
                                else:
                                    new_font = Font(size=font_size)
                                cell.font = new_font
                elif range_type == "cols" and range_value:
                    cols = parse_range_expression(range_value)
                    for col in cols:
                        if isinstance(col, str) and col.isalpha():
                            for cell in sheet[col]:
                                if cell.font:
                                    new_font = Font(size=font_size, name=cell.font.name, bold=cell.font.bold,
                                                   italic=cell.font.italic, underline=cell.font.underline)
                                else:
                                    new_font = Font(size=font_size)
                                cell.font = new_font
                elif range_type == "cells" and range_value:
                    cells = [cell.strip() for cell in range_value.split(',')]
                    for cell_ref in cells:
                        try:
                            cell = sheet[cell_ref]
                            if cell.font:
                                new_font = Font(size=font_size, name=cell.font.name, bold=cell.font.bold,
                                               italic=cell.font.italic, underline=cell.font.underline)
                            else:
                                new_font = Font(size=font_size)
                            cell.font = new_font
                        except Exception:
                            continue

        status_bar.config(text=f"字体大小已更新为 {font_size}")
        font_size_options.destroy()
        messagebox.showinfo("完成", "字体大小已修改!")

    # 按钮
    button_frame = ttk.Frame(settings_frame)
    button_frame.pack(fill=tk.X, pady=10)
    ttk.Button(button_frame, text="应用", command=apply).pack(side=tk.RIGHT)
    ttk.Button(button_frame, text="取消", command=font_size_options.destroy).pack(side=tk.RIGHT, padx=5)


def apply_font_name():
    font_options = tk.Toplevel(root)
    font_options.title("修改字体")
    font_options.geometry("590x660")

    settings_frame = ttk.Frame(font_options)
    settings_frame.pack(fill=tk.BOTH, expand=True, padx=10, pady=5)

    # 常用字体选择
    font_frame = ttk.LabelFrame(settings_frame, text="常用字体")
    font_frame.pack(fill=tk.X, pady=5)

    common_fonts = [
        "宋体", "微软雅黑", "黑体", "Arial",
        "Times New Roman", "Calibri", "仿宋", "楷体"
    ]

    font_var = tk.StringVar()
    for font in common_fonts:
        ttk.Radiobutton(font_frame, text=font, variable=font_var, value=font).pack(anchor=tk.W)

    # 自定义字体输入
    custom_frame = ttk.LabelFrame(settings_frame, text="自定义字体")
    custom_frame.pack(fill=tk.X, pady=5)

    ttk.Label(custom_frame, text="字体名称:").pack(side=tk.LEFT)
    custom_entry = ttk.Entry(custom_frame)
    custom_entry.pack(side=tk.LEFT, fill=tk.X, expand=True)

    # 范围选择
    range_frame = ttk.LabelFrame(settings_frame, text="应用范围")
    range_frame.pack(fill=tk.X, pady=5)

    range_var = tk.StringVar(value="all")
    ttk.Radiobutton(range_frame, text="所有单元格", variable=range_var, value="all").pack(anchor=tk.W)
    ttk.Radiobutton(range_frame, text="选定行", variable=range_var, value="rows").pack(anchor=tk.W)
    ttk.Radiobutton(range_frame, text="选定列", variable=range_var, value="cols").pack(anchor=tk.W)
    ttk.Radiobutton(range_frame, text="指定单元格", variable=range_var, value="cells").pack(anchor=tk.W)

    # 范围输入
    input_frame = ttk.Frame(range_frame)
    input_frame.pack(fill=tk.X, pady=5)
    ttk.Label(input_frame, text="范围:").pack(side=tk.LEFT)
    range_entry = ttk.Entry(input_frame)
    range_entry.pack(side=tk.LEFT, fill=tk.X, expand=True)
    ttk.Label(input_frame, text="(例如: A1,B2 或 1,2 或 A,B)").pack(side=tk.LEFT)

    def apply():
        try:
            # 获取字体名称（优先使用常用字体选择，如果没有选择则使用自定义输入）
            font_name = font_var.get() or custom_entry.get().strip()
            if not font_name:
                messagebox.showerror("错误", "请选择或输入字体名称!")
                return

            range_type = range_var.get()
            range_value = range_entry.get().strip()

            for wb in workbooks.values():
                for sheet in wb:
                    if range_type == "all":
                        for row in sheet.iter_rows():
                            for cell in row:
                                cell.font = Font(name=font_name)
                    elif range_type == "rows" and range_value:
                        rows = [int(r.strip()) for r in range_value.split(',') if r.strip().isdigit()]
                        for row in rows:
                            for cell in sheet[row]:
                                cell.font = Font(name=font_name)
                    elif range_type == "cols" and range_value:
                        cols = [col.strip().upper() for col in range_value.split(',') if col.strip().isalpha()]
                        for col in cols:
                            for cell in sheet[col]:
                                cell.font = Font(name=font_name)
                    elif range_type == "cells" and range_value:
                        cells = [cell.strip() for cell in range_value.split(',')]
                        for cell_ref in cells:
                            cell = sheet[cell_ref]
                            cell.font = Font(name=font_name)

            status_bar.config(text=f"字体已更新为 {font_name}")
            font_options.destroy()
            messagebox.showinfo("完成", "字体已修改!")
        except Exception as e:
            messagebox.showerror("错误", f"修改过程中发生错误: {str(e)}")

    # 按钮区域
    button_frame = ttk.Frame(settings_frame)
    button_frame.pack(fill=tk.X, pady=10)
    ttk.Button(button_frame, text="应用", command=apply).pack(side=tk.RIGHT)
    ttk.Button(button_frame, text="取消", command=font_options.destroy).pack(side=tk.RIGHT, padx=5)


def apply_font_color():
    color_options = tk.Toplevel(root)
    color_options.title("修改字体颜色")
    color_options.geometry("550x450")

    # 创建设置框架
    settings_frame = ttk.Frame(color_options)
    settings_frame.pack(fill=tk.BOTH, expand=True, padx=10, pady=5)

    # 预设颜色框架
    preset_frame = ttk.LabelFrame(settings_frame, text="常用颜色")
    preset_frame.pack(fill=tk.X, pady=5)

    preset_colors = [
        ("#000000", "黑色"), ("#FF0000", "红色"),
        ("#00FF00", "绿色"), ("#0000FF", "蓝色"),
        ("#FF00FF", "紫色"), ("#FFD700", "金色")
    ]

    color_var = tk.StringVar()
    for i, (color_code, color_name) in enumerate(preset_colors):
        ttk.Radiobutton(preset_frame, text=color_name, variable=color_var,
                        value=color_code).grid(row=i // 3, column=i % 3, padx=5, pady=2)

    # 范围选择框架
    range_frame = ttk.LabelFrame(settings_frame, text="应用范围")
    range_frame.pack(fill=tk.X, pady=5)

    range_var = tk.StringVar(value="all")
    ttk.Radiobutton(range_frame, text="所有单元格", variable=range_var, value="all").pack(anchor=tk.W)
    ttk.Radiobutton(range_frame, text="选定行", variable=range_var, value="rows").pack(anchor=tk.W)
    ttk.Radiobutton(range_frame, text="选定列", variable=range_var, value="cols").pack(anchor=tk.W)
    ttk.Radiobutton(range_frame, text="指定单元格", variable=range_var, value="cells").pack(anchor=tk.W)

    # 范围输入框
    input_frame = ttk.Frame(range_frame)
    input_frame.pack(fill=tk.X, pady=5)
    ttk.Label(input_frame, text="范围:").pack(side=tk.LEFT)
    range_entry = ttk.Entry(input_frame)
    range_entry.pack(side=tk.LEFT, fill=tk.X, expand=True)
    ttk.Label(input_frame, text="(例如: A1,B2 或 1,2 或 A,B)").pack(side=tk.LEFT)

    def custom_color():
        color = colorchooser.askcolor(title="选择自定义颜色")[1]
        if color:
            color_var.set(color)

    ttk.Button(settings_frame, text="选择自定义颜色", command=custom_color).pack(pady=5)

    def apply():
        color = color_var.get()
        if not color:
            color = colorchooser.askcolor(title="选择字体颜色")[1]
            if not color:
                return

        range_type = range_var.get()
        range_value = range_entry.get().strip()

        try:
            for wb in workbooks.values():
                for sheet in wb:
                    if range_type == "all":
                        for row in sheet.iter_rows():
                            for cell in row:
                                cell.font = Font(color=color.replace('#', ''))
                    elif range_type == "rows" and range_value:
                        rows = [int(r.strip()) for r in range_value.split(',') if r.strip().isdigit()]
                        for row_num in rows:
                            for cell in sheet[row_num]:
                                cell.font = Font(color=color.replace('#', ''))
                    elif range_type == "cols" and range_value:
                        cols = [col.strip().upper() for col in range_value.split(',') if col.strip().isalpha()]
                        for col in cols:
                            for cell in sheet[col]:
                                cell.font = Font(color=color.replace('#', ''))
                    elif range_type == "cells" and range_value:
                        cells = [cell.strip() for cell in range_value.split(',')]
                        for cell_ref in cells:
                            sheet[cell_ref].font = Font(color=color.replace('#', ''))

            status_bar.config(text="字体颜色已更新")
            color_options.destroy()
            messagebox.showinfo("完成", "字体颜色已修改!")
        except Exception as e:
            messagebox.showerror("错误", f"修改过程中发生错误: {str(e)}")

    # 按钮
    button_frame = ttk.Frame(settings_frame)
    button_frame.pack(fill=tk.X, pady=10)
    ttk.Button(button_frame, text="应用", command=apply).pack(side=tk.RIGHT)
    ttk.Button(button_frame, text="取消", command=color_options.destroy).pack(side=tk.RIGHT, padx=5)


def parse_and_apply_color(sheet, color, cell_range):
    # 解析用户输入的范围
    ranges = cell_range.split(',')
    for rng in ranges:
        if ':' in rng:  # 处理行或列
            if rng.count(':') == 1 and all(x.isalpha() for x in rng.replace(':', '')):  # 处理整列如A:A
                col_idx = column_index_from_string(rng.split(':')[0])
                for row in sheet.iter_rows():
                    cell = row[col_idx - 1]
                    cell.font = Font(color=color.replace('#', ''), size=cell.font.size if cell.font else 11)
            elif rng.count(':') == 1 and all(x.isdigit() for x in rng.replace(':', '')):  # 处理整行如1:1
                row_idx = int(rng.split(':')[0])
                for cell in sheet[row_idx]:
                    cell.font = Font(color=color.replace('#', ''), size=cell.font.size if cell.font else 11)
        else:  # 处理单个单元格
            cell = sheet[rng]
            cell.font = Font(color=color.replace('#', ''), size=cell.font.size if cell.font else 11)


def parse_range_expression(range_expr):
    """解析范围表达式，支持以下格式：
    1. 单个值：1, 2, A, B
    2. 范围值：1-5, A-C
    3. 混合格式：1-5,7,9-11 或 A-C,E,G-I
    返回值的列表
    """
    result = []
    if not range_expr or not range_expr.strip():
        return result
    
    parts = range_expr.strip().split(',')
    for part in parts:
        part = part.strip()
        if '-' in part:
            # 处理范围表达式 如 1-5 或 A-C
            start, end = part.split('-', 1)
            start = start.strip()
            end = end.strip()
            
            if start.isdigit() and end.isdigit():
                # 数字范围
                start_num, end_num = int(start), int(end)
                result.extend(range(start_num, end_num + 1))
            elif start.isalpha() and end.isalpha():
                # 字母范围，假设都是单个大写字母
                start = start.upper()
                end = end.upper()
                start_ord = ord(start)
                end_ord = ord(end)
                result.extend([chr(i) for i in range(start_ord, end_ord + 1)])
        else:
            # 单个值
            if part.isdigit():
                result.append(int(part))
            elif part.isalpha():
                result.append(part.upper())
    
    return result


def get_column_letter(cell):
    """安全地获取单元格的列字母（兼容 openpyxl 不同版本）"""
    from openpyxl.utils import get_column_letter as _get_column_letter
    try:
        # openpyxl 3.x 推荐直接使用 cell.column_letter
        if hasattr(cell, "column_letter") and cell.column_letter:
            return cell.column_letter
        # openpyxl 2.x 中 cell.column 可能是列索引（int）或列字母（str）
        col_attr = getattr(cell, "column", None)
        if isinstance(col_attr, int):
            return _get_column_letter(col_attr)
        if isinstance(col_attr, str) and col_attr.isalpha():
            return col_attr.upper()
        # 最后从坐标中提取
        coord = getattr(cell, "coordinate", "")
        return "".join(c for c in coord if c.isalpha()).upper() or None
    except Exception:
        return None


def apply_column_width():
    width_options = tk.Toplevel(root)
    width_options.title("修改列宽")
    width_options.geometry("750x400")

    settings_frame = ttk.Frame(width_options)
    settings_frame.pack(fill=tk.BOTH, expand=True, padx=10, pady=5)

    # 列宽选择
    width_frame = ttk.LabelFrame(settings_frame, text="列宽设置")
    width_frame.pack(fill=tk.X, pady=5)

    # 使用数字输入而非滑块，支持小数
    width_var = tk.DoubleVar(value=10.0)
    ttk.Label(width_frame, text="列宽:").pack(side=tk.LEFT, padx=5)
    width_entry = ttk.Entry(width_frame, textvariable=width_var)
    width_entry.pack(side=tk.LEFT, fill=tk.X, expand=True, padx=5)

    # 常用宽度预设
    preset_frame = ttk.LabelFrame(settings_frame, text="常用宽度")
    preset_frame.pack(fill=tk.X, pady=5)
    presets = [(8, "窄"), (15, "标准"), (25, "宽"), (40, "特宽")]

    def apply_preset(value):
        width_var.set(value)

    for value, name in presets:
        ttk.Button(preset_frame, text=name, command=lambda v=value: apply_preset(v)).pack(side=tk.LEFT, padx=5,
                                                                                          expand=True)

    # 列选择
    columns_frame = ttk.LabelFrame(settings_frame, text="选择列")
    columns_frame.pack(fill=tk.X, pady=5)

    range_var = tk.StringVar(value="all")
    ttk.Radiobutton(columns_frame, text="所有列", variable=range_var, value="all").pack(anchor=tk.W)
    ttk.Radiobutton(columns_frame, text="选定列", variable=range_var, value="selected").pack(anchor=tk.W)

    cols_frame = ttk.Frame(columns_frame)
    cols_frame.pack(fill=tk.X, pady=5)
    ttk.Label(cols_frame, text="列号:").pack(side=tk.LEFT)
    cols_entry = ttk.Entry(cols_frame)
    cols_entry.pack(side=tk.LEFT, fill=tk.X, expand=True)
    ttk.Label(cols_frame, text="(例如: A,B,C 或 A-F,H,J-L)").pack(side=tk.LEFT)

    # 显示原列宽
    original_width_var = tk.StringVar(value="原列宽: --")
    original_width_label = ttk.Label(columns_frame, textvariable=original_width_var)
    original_width_label.pack(anchor=tk.W, pady=2)

    def update_original_width(*args):
        """根据输入列显示第一张工作表对应列的当前列宽"""
        try:
            if not workbooks:
                original_width_var.set("原列宽: --")
                return
            column_letters = parse_range_expression(cols_entry.get())
            if not column_letters:
                original_width_var.set("原列宽: --")
                return
            first_col = None
            for col in column_letters:
                if isinstance(col, str) and col.isalpha():
                    first_col = col
                    break
            if not first_col:
                original_width_var.set("原列宽: --")
                return
            # 取第一本工作簿第一个工作表的列宽
            wb = next(iter(workbooks.values()))
            sheet = wb.active
            width = sheet.column_dimensions[first_col].width
            width_display = width if width is not None else "默认"
            original_width_var.set(f"原列宽: {width_display}")
        except Exception:
            original_width_var.set("原列宽: --")

    # 绑定事件，实时更新原列宽
    cols_entry.bind("<KeyRelease>", update_original_width)

    def apply():
        try:
            column_width = float(width_var.get())
            for wb in workbooks.values():
                for sheet in wb:
                    if range_var.get() == "all":
                        # 使用一个集合来跟踪已处理的列
                        processed_columns = set()
                        for row in sheet.iter_rows():
                            for cell in row:
                                col_letter = get_column_letter(cell)
                                if col_letter and col_letter not in processed_columns:
                                    sheet.column_dimensions[col_letter].width = column_width
                                    processed_columns.add(col_letter)
                    else:
                        # 使用新的范围解析函数处理列范围
                        column_letters = parse_range_expression(cols_entry.get())
                        for col in column_letters:
                            if isinstance(col, str) and col.strip():
                                sheet.column_dimensions[col.strip()].width = column_width

            status_bar.config(text=f"列宽已更新为 {column_width}")
            width_options.destroy()
            messagebox.showinfo("完成", "列宽已修改!")
        except Exception as e:
            messagebox.showerror("错误", f"修改过程中发生错误: {str(e)}")

    # 按钮区域
    button_frame = ttk.Frame(settings_frame)
    button_frame.pack(fill=tk.X, pady=10)
    ttk.Button(button_frame, text="应用", command=apply).pack(side=tk.RIGHT)
    ttk.Button(button_frame, text="取消", command=width_options.destroy).pack(side=tk.RIGHT, padx=5)


def apply_row_height():
    height_options = tk.Toplevel(root)
    height_options.title("修改行高")
    height_options.geometry("750x400")

    settings_frame = ttk.Frame(height_options)
    settings_frame.pack(fill=tk.BOTH, expand=True, padx=10, pady=5)

    # 行高选择
    height_frame = ttk.LabelFrame(settings_frame, text="行高设置")
    height_frame.pack(fill=tk.X, pady=5)

    height_var = tk.IntVar(value=15)
    height_scale = ttk.Scale(height_frame, from_=1, to=100, variable=height_var,
                             orient=tk.HORIZONTAL, command=lambda x: height_var.set(round(float(x))))
    height_scale.pack(fill=tk.X, padx=10, pady=5)
    height_label = ttk.Label(height_frame, textvariable=height_var)
    height_label.pack()

    # 常用高度预设
    preset_frame = ttk.LabelFrame(settings_frame, text="常用高度")
    preset_frame.pack(fill=tk.X, pady=5)
    presets = [(15, "标准"), (20, "较高"), (30, "高"), (45, "特高")]

    def apply_preset(value):
        height_var.set(value)

    for value, name in presets:
        ttk.Button(preset_frame, text=name, command=lambda v=value: apply_preset(v)).pack(side=tk.LEFT, padx=5,
                                                                                          expand=True)

    # 行选择
    rows_frame = ttk.LabelFrame(settings_frame, text="选择行")
    rows_frame.pack(fill=tk.X, pady=5)

    range_var = tk.StringVar(value="all")
    ttk.Radiobutton(rows_frame, text="所有行", variable=range_var, value="all").pack(anchor=tk.W)
    ttk.Radiobutton(rows_frame, text="选定行", variable=range_var, value="selected").pack(anchor=tk.W)

    row_input_frame = ttk.Frame(rows_frame)
    row_input_frame.pack(fill=tk.X, pady=5)
    ttk.Label(row_input_frame, text="行号:").pack(side=tk.LEFT)
    rows_entry = ttk.Entry(row_input_frame)
    rows_entry.pack(side=tk.LEFT, fill=tk.X, expand=True)
    ttk.Label(row_input_frame, text="(例如: 1,2,3 或 1-10,15,20-30)").pack(side=tk.LEFT)

    def apply():
        try:
            row_height = height_var.get()
            for wb in workbooks.values():
                for sheet in wb:
                    if range_var.get() == "all":
                        # 使用一个集合来跟踪已处理的行
                        processed_rows = set()
                        for row in sheet.iter_rows():
                            for cell in row:
                                if hasattr(cell, 'row') and cell.row not in processed_rows:
                                    sheet.row_dimensions[cell.row].height = row_height
                                    processed_rows.add(cell.row)
                    else:
                        # 使用新的范围解析函数处理行范围
                        row_numbers = parse_range_expression(rows_entry.get())
                        for row in row_numbers:
                            if isinstance(row, int):
                                sheet.row_dimensions[row].height = row_height

            status_bar.config(text=f"行高已更新为 {row_height}")
            height_options.destroy()
            messagebox.showinfo("完成", "行高已修改!")
        except Exception as e:
            messagebox.showerror("错误", f"修改过程中发生错误: {str(e)}")

    # 按钮区域
    button_frame = ttk.Frame(settings_frame)
    button_frame.pack(fill=tk.X, pady=10)
    ttk.Button(button_frame, text="应用", command=apply).pack(side=tk.RIGHT)
    ttk.Button(button_frame, text="取消", command=height_options.destroy).pack(side=tk.RIGHT, padx=5)


def apply_text_style():
    style_options = tk.Toplevel(root)
    style_options.title("修改文本样式")
    style_options.geometry("560x420")

    settings_frame = ttk.Frame(style_options)
    settings_frame.pack(fill=tk.BOTH, expand=True, padx=10, pady=5)

    # 样式选择
    style_frame = ttk.LabelFrame(settings_frame, text="文本样式")
    style_frame.pack(fill=tk.X, pady=5)

    bold_var = tk.BooleanVar()
    italic_var = tk.BooleanVar()
    underline_var = tk.BooleanVar()

    ttk.Checkbutton(style_frame, text="粗体", variable=bold_var).pack(anchor=tk.W)
    ttk.Checkbutton(style_frame, text="斜体", variable=italic_var).pack(anchor=tk.W)
    ttk.Checkbutton(style_frame, text="下划线", variable=underline_var).pack(anchor=tk.W)

    # 范围选择
    range_frame = ttk.LabelFrame(settings_frame, text="应用范围")
    range_frame.pack(fill=tk.X, pady=5)

    range_var = tk.StringVar(value="all")
    ttk.Radiobutton(range_frame, text="所有单元格", variable=range_var, value="all").pack(anchor=tk.W)
    ttk.Radiobutton(range_frame, text="选定行", variable=range_var, value="rows").pack(anchor=tk.W)
    ttk.Radiobutton(range_frame, text="选定列", variable=range_var, value="cols").pack(anchor=tk.W)
    ttk.Radiobutton(range_frame, text="指定单元格", variable=range_var, value="cells").pack(anchor=tk.W)

    # 范围输入
    input_frame = ttk.Frame(range_frame)
    input_frame.pack(fill=tk.X, pady=5)
    ttk.Label(input_frame, text="范围:").pack(side=tk.LEFT)
    range_entry = ttk.Entry(input_frame)
    range_entry.pack(side=tk.LEFT, fill=tk.X, expand=True)
    ttk.Label(input_frame, text="(例如: A1,B2 或 1,2 或 A,B)").pack(side=tk.LEFT)

    def apply():
        try:
            is_bold = bold_var.get()
            is_italic = italic_var.get()
            is_underline = 'single' if underline_var.get() else None  # 修改这行

            range_type = range_var.get()
            range_value = range_entry.get().strip()

            for wb in workbooks.values():
                for sheet in wb:
                    if range_type == "all":
                        for row in sheet.iter_rows():
                            for cell in row:
                                cell.font = Font(bold=is_bold, italic=is_italic, underline=is_underline)
                    elif range_type == "rows" and range_value:
                        rows = [int(r.strip()) for r in range_value.split(',') if r.strip().isdigit()]
                        for row in rows:
                            for cell in sheet[row]:
                                cell.font = Font(bold=is_bold, italic=is_italic, underline=is_underline)
                    elif range_type == "cols" and range_value:
                        cols = [col.strip().upper() for col in range_value.split(',') if col.strip().isalpha()]
                        for col in cols:
                            for cell in sheet[col]:
                                cell.font = Font(bold=is_bold, italic=is_italic, underline=is_underline)
                    elif range_type == "cells" and range_value:
                        cells = [cell.strip() for cell in range_value.split(',')]
                        for cell_ref in cells:
                            cell = sheet[cell_ref]
                            cell.font = Font(bold=is_bold, italic=is_italic, underline=is_underline)

            status_bar.config(text="文本样式已更新")
            style_options.destroy()
            messagebox.showinfo("完成", "文本样式已修改!")
        except Exception as e:
            messagebox.showerror("错误", f"修改过程中发生错误: {str(e)}")

    # 按钮区域
    button_frame = ttk.Frame(settings_frame)
    button_frame.pack(fill=tk.X, pady=10)
    ttk.Button(button_frame, text="应用", command=apply).pack(side=tk.RIGHT)
    ttk.Button(button_frame, text="取消", command=style_options.destroy).pack(side=tk.RIGHT, padx=5)


# 新增：选定单元格居中
def apply_alignment_cells():
    align_win = tk.Toplevel(root)
    align_win.title("选定单元格居中")
    align_win.geometry("400x150")

    ttk.Label(align_win, text="输入单元格（用逗号分隔，如 A1,B2,C3）:").pack(pady=5)
    cell_entry = ttk.Entry(align_win)
    cell_entry.pack(fill=tk.X, padx=10, pady=5)

    def apply():
        alignment = Alignment(horizontal='center', vertical='center')
        cells = [c.strip() for c in cell_entry.get().split(',') if c.strip()]
        if not cells:
            messagebox.showerror("错误", "请输入单元格!")
            return
        try:
            for wb in workbooks.values():
                for sheet in wb:
                    for ref in cells:
                        try:
                            cell = sheet[ref]
                            apply_to_cell(cell, sheet, alignment=alignment)
                        except Exception:
                            # 跳过无效引用
                            continue
            status_bar.config(text="指定单元格已居中对齐")
            align_win.destroy()
            messagebox.showinfo("完成", "选定单元格已居中!")
        except Exception as e:
            messagebox.showerror("错误", f"修改过程中发生错误: {str(e)}")

    ttk.Button(align_win, text="应用", command=apply).pack(pady=10)


# 新增：选定单元格自动换行
def apply_wrap_text_cells():
    wrap_win = tk.Toplevel(root)
    wrap_win.title("选定单元格自动换行")
    wrap_win.geometry("400x150")

    ttk.Label(wrap_win, text="输入单元格（用逗号分隔，如 A1,B2,C3）:").pack(pady=5)
    cell_entry = ttk.Entry(wrap_win)
    cell_entry.pack(fill=tk.X, padx=10, pady=5)

    def apply():
        cells = [c.strip() for c in cell_entry.get().split(',') if c.strip()]
        if not cells:
            messagebox.showerror("错误", "请输入单元格!")
            return
        try:
            for wb in workbooks.values():
                for sheet in wb:
                    for ref in cells:
                        try:
                            cell = sheet[ref]
                            # 保留原有对齐方式，仅设置自动换行
                            current_alignment = cell.alignment or Alignment()
                            new_alignment = Alignment(horizontal=current_alignment.horizontal,
                                                      vertical=current_alignment.vertical,
                                                      wrap_text=True)
                            apply_to_cell(cell, sheet, alignment=new_alignment)
                        except Exception:
                            continue
            status_bar.config(text="指定单元格已设置自动换行")
            wrap_win.destroy()
            messagebox.showinfo("完成", "选定单元格已自动换行!")
        except Exception as e:
            messagebox.showerror("错误", f"修改过程中发生错误: {str(e)}")

    ttk.Button(wrap_win, text="应用", command=apply).pack(pady=10)


# 新增：选定列左/右对齐（可指定起始行）
def apply_column_alignment():
    align_col_win = tk.Toplevel(root)
    align_col_win.title("选定列对齐")
    align_col_win.geometry("450x220")

    # 列输入
    ttk.Label(align_col_win, text="输入列（如 A,B 或 A-D,E）:").pack(anchor=tk.W, pady=5, padx=10)
    col_entry = ttk.Entry(align_col_win)
    col_entry.pack(fill=tk.X, padx=10)

    # 起始行输入
    row_frame = ttk.Frame(align_col_win)
    row_frame.pack(fill=tk.X, padx=10, pady=5)
    ttk.Label(row_frame, text="起始行:").pack(side=tk.LEFT)
    start_row_var = tk.StringVar(value="1")
    ttk.Entry(row_frame, textvariable=start_row_var, width=10).pack(side=tk.LEFT)
    ttk.Label(row_frame, text="(从该行开始向下对齐)").pack(side=tk.LEFT)

    # 对齐方向
    direction_var = tk.StringVar(value="left")
    dir_frame = ttk.LabelFrame(align_col_win, text="对齐方式")
    dir_frame.pack(fill=tk.X, padx=10, pady=5)
    ttk.Radiobutton(dir_frame, text="左对齐", variable=direction_var, value="left").pack(side=tk.LEFT, padx=5)
    ttk.Radiobutton(dir_frame, text="右对齐", variable=direction_var, value="right").pack(side=tk.LEFT, padx=5)

    def apply():
        cols = parse_range_expression(col_entry.get())
        if not cols:
            messagebox.showerror("错误", "请输入有效列!")
            return
        try:
            start_row = int(start_row_var.get()) if start_row_var.get().isdigit() else 1
        except ValueError:
            start_row = 1
        horiz = 'left' if direction_var.get() == 'left' else 'right'
        try:
            for wb in workbooks.values():
                for sheet in wb:
                    for col in cols:
                        if isinstance(col, str) and col.isalpha():
                            for cell in sheet[col]:
                                if cell.row >= start_row:
                                    current = cell.alignment or Alignment()
                                    new_align = Alignment(horizontal=horiz,
                                                          vertical=current.vertical,
                                                          wrap_text=current.wrap_text)
                                    apply_to_cell(cell, sheet, alignment=new_align)
        except Exception as e:
            messagebox.showerror("错误", f"修改过程中发生错误: {str(e)}")
            return
        status_bar.config(text=f"列已设置为{ '左' if horiz=='left' else '右' }对齐 (自第{start_row}行起)")
        align_col_win.destroy()
        messagebox.showinfo("完成", "列对齐已修改!")

    ttk.Button(align_col_win, text="应用", command=apply).pack(pady=10)


# 创建框架
# 替换原有的frame创建代码
container = ttk.Frame(main_frame)
container.pack(fill=tk.BOTH, expand=True)

# 两列布局：左侧文件操作，右侧格式设置
container.grid_columnconfigure(0, weight=1, uniform="col")
container.grid_columnconfigure(1, weight=2, uniform="col")
container.grid_rowconfigure(0, weight=1)

frame_files = ttk.LabelFrame(container, text="文件操作", padding=10)
frame_files.grid(row=0, column=0, sticky="nsew", padx=5, pady=5)

frame_format = ttk.LabelFrame(container, text="格式设置", padding=10)
frame_format.grid(row=0, column=1, sticky="nsew", padx=5, pady=5)

# 文件操作按钮（垂直）
# 主题切换
_theme_frame = ttk.LabelFrame(frame_files, text="主题")
_theme_frame.pack(fill=tk.X, pady=(0, 6))
try:
    _theme_var = tk.StringVar(value=style.theme_use())
    _themes = [t for t in style.theme_names() if t not in ("plastik",)]  # 略去较重主题
    ttk.Combobox(_theme_frame, textvariable=_theme_var, values=_themes, state="readonly")\
        .pack(fill=tk.X, pady=2)
    def _on_theme_change(*_):
        try:
            style.theme_use(_theme_var.get())
        except Exception:
            pass
    _theme_var.trace_add("write", _on_theme_change)
except Exception:
    pass

button_load = ttk.Button(frame_files, text="加载所有 Excel 文件", command=load_all_excels)
button_load.pack(fill=tk.X, pady=4)

button_save = ttk.Button(frame_files, text="保存修改", command=save_excels)
button_save.pack(fill=tk.X, pady=4)

button_restore = ttk.Button(frame_files, text="还原所有更改", command=restore_originals)
button_restore.pack(fill=tk.X, pady=4)

# 格式设置按钮（网格 4 列）
_format_buttons = [
    ("全部居中", apply_alignment),
    ("单元格居中", apply_alignment_cells),
    ("修改背景颜色", apply_fill_color),
    ("修改字体大小", apply_font_size),
    ("修改列宽", apply_column_width),
    ("修改行高", apply_row_height),
    ("列对齐", apply_column_alignment),
    ("修改字体名", apply_font_name),
    ("修改字体颜色", apply_font_color),
    ("修改文本样式", apply_text_style),
    ("自动换行", apply_wrap_text_cells),
]
for idx, (text, cmd) in enumerate(_format_buttons):
    r, c = divmod(idx, 4)
    ttk.Button(frame_format, text=text, command=cmd).grid(row=r, column=c, padx=6, pady=6, sticky="ew")
for i in range(4):
    frame_format.grid_columnconfigure(i, weight=1)

# 在root.mainloop()之前添加这两行
# 在root.mainloop()之前添加这两行
status_bar = ttk.Label(root, text="就绪", relief=tk.SUNKEN, anchor=tk.W)
status_bar.pack(side=tk.BOTTOM, fill=tk.X)


# 添加新的关闭处理函数
def on_closing():
    try:
        clean_up()
    except Exception as e:
        print(f"清理时出现错误: {e}")
    finally:
        root.destroy()


# 设置窗口关闭协议
root.protocol("WM_DELETE_WINDOW", on_closing)

# 启动主循环
root.mainloop()
